﻿using Microsoft.Reporting.WinForms;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace CMSWindowsFormsApp2
{
    public partial class WorkerTotalJobReportForm : Form
    {
        public WorkerTotalJobReportForm()
        {
            InitializeComponent();
        }

        private void WorkerTotalJobReportForm_Load(object sender, EventArgs e)
        {

            // Create the dataset and fill it with data
            DataTable dt = GetConsumableData(); // Implement this function to fetch data
            ReportDataSource rds = new ReportDataSource("WorkerTotalJobDataSet", dt);

            // Clear previous data sources and assign the new one
            this.reportViewer1.LocalReport.DataSources.Clear();
            this.reportViewer1.LocalReport.DataSources.Add(rds);


            this.reportViewer1.RefreshReport();
        }

        private DataTable GetConsumableData()
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection("Data Source=DESKTOP-SR1MKQK;Initial Catalog=CMSDATABASE;Integrated Security=True"))
            {
                string query = "SELECT w.Name AS WorkerName,YEAR(j.JobDate) AS JobYear, MONTH(j.JobDate) AS JobMonth, COUNT(*) AS TotalJobs FROM tblJobDetails j JOIN tblWorker w ON j.WorkerID = w.WorkerID WHERE j.JobDate BETWEEN DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AND GETDATE() GROUP BY w.Name, YEAR(j.JobDate), MONTH(j.JobDate) ORDER BY JobYear, JobMonth, w.Name";

                SqlDataAdapter da = new SqlDataAdapter(query, conn);
                da.Fill(dt);
            }
            return dt;
        }
    }
}
